/**
 * Created by kevenfeng on 2017/9/26.
 */
var mysql = require('mysql');
var Promise = require('bluebird');
var path = require('path');
var fs = require("fs");

//创建连接池
var filename = path.join(__dirname, "..", "config", "db.conf");
var dbconfig = fs.readFileSync(filename, {
    encoding: "utf-8"
});
var serverDbConfig = JSON.parse(dbconfig)['testyhdb'];

var poolConnection = mysql.createPool({
    host: serverDbConfig.host,
    user: serverDbConfig.user,
    password: serverDbConfig.password,
    database: serverDbConfig.database
});



var commonDb = function () {
    this.init();
};

var fn = commonDb.prototype;

fn.init = function () {
    this.pool = poolConnection;
}

fn.executeMysql = function (options) {
    var sql = options.sql;
    var params = options.params;
    var that = this;
    return new Promise(function (resolve, reject) {
        that.pool.getConnection(function (err, connection) {
            if (err) {
                console.log("连接数据库失败:", err);
                return;
            }
            var query = connection.query(sql, params, function (error, result, fields) {
                connection.release();
                if (error) {
                    reject(error);
                } else {
                    resolve(result);
                };
            });
            console.log("sql:", query.sql);
        })
    });
}

fn.getAllDataInit = function (options) {
    //console.log("options:", options);
    this.limit = options.limit;
    this.offset = options.offset;
    this.tableName = this.pool.escapeId(options.table);
    this.order = this.order(options.order);
    // this.wheres = this.where(options.wheres);
    this.likes = options.like;
    if (this.likes == "" || this.likes.value == "") {
        if (options.wheres == "") {
            this.wheres = true;
        } else {
            this.wheres = this.where(options.wheres);
        }
    } else {
        if (options.wheres == "") {
            this.wheres = this.likes.key + " LIKE %" + this.likes.value + "%";
        } else {
            this.wheres = this.where(options.wheres) + " AND " + this.likes.key + " LIKE '%" + this.likes.value + "%'";
        }
    }


    if (options.orderType == 'asc') {
        this.orderType = "asc";
    } else {
        this.orderType = "desc";
    }
}

fn.order = function (data) {
    var order = "";
    if (data.indexOf(",") != -1) {
        var orderArray = data.split(",");
        for (var i = 0, j = orderArray.length; i < j; i++) {
            orderArray[i] = this.pool.escapeId(orderArray[i]);
        }
        order = orderArray.join(",");
    } else {
        order = this.pool.escapeId(data);
    }

    return order;
}

fn.where = function (wheres) {
    //console.log("wheres:", wheres);
    var wheresCondition = "";
    if (wheres && wheres != "") {
        var wheresArray = [];

        for (var i = 0; i < wheres.length; i++) {
            if (wheres[i].value === "" || wheres[i].value === undefined) {

            } else {
                wheresArray.push(wheres[i]);
            }
        }

        for (var i = 0; i < wheresArray.length; i++) {
            if (i == wheresArray.length - 1) {
                wheresCondition += this.whereCellDeal(wheresArray[i]);
            } else {
                wheresCondition += this.whereCellDeal(wheresArray[i]) + ' and ';
            }
        }
    }

    if (wheresCondition == "") {
        wheresCondition = true;
    }
    return wheresCondition;
}

fn.whereCellDeal = function (where) {
    var sqlString = "";

    if (where.type == 1) { //相等
        sqlString = this.pool.escapeId(where.key) + "=" + this.pool.escape(where.value);
    } else {
        sqlString = where.key + "=" + where.value;
    }
    return sqlString;
}

fn.getTotleNumb = function () {
    var options = {
        sql: 'SELECT count(*) as totleNumb FROM ' + this.tableName + ' where ' + this.wheres + ';',
        params: []
    }
    return this.executeMysql(options);
}

fn.getData = function () {
    var options = {
        sql: 'SELECT * FROM ' + this.tableName + ' where ' + this.wheres + ' order by ' + this.order + " " + this.orderType + ' limit ?,?;',
        params: [this.offset, this.limit]
    }
    return this.executeMysql(options);
}

fn.getAllDataList = function (options) {
    this.getAllDataInit(options);

    var queue = [];
    queue.push(this.getTotleNumb());
    queue.push(this.getData());
    return Promise.all(queue);
}

fn.getDataAll = function () {
    var options = {
        sql: 'SELECT * FROM ' + this.tableName + ' where ' + this.wheres + ' order by ' + this.order + " " + this.orderType + ';',
        params: []
    }
    return this.executeMysql(options);
}

fn.refreshDataList = function (options) {
    this.getAllDataInit(options);

    var queueArr = [];
    queueArr.push(this.getTotleNumb());
    queueArr.push(this.getDataAll());
    return Promise.all(queueArr);
}


fn.addOrEditOrDelete = function (options) {
    var formData = options.form;
    var tableName = this.pool.escapeId(options.tableName);

    var sqlString = "";

    if (formData.id) { //更新数据
        var time = new Date();
        formData.update_time = time;
        if (formData.create_time && !(formData.create_time instanceof Date)) {
            formData.create_time = new Date(formData.create_time);
        }
        sqlString = "UPDATE " + tableName + " SET ? WHERE " + tableName + ".`id` = " + formData.id;
        delete formData.id;
    } else { //新增数据
        delete formData.id;
        var time = new Date();
        formData.create_time = time;
        formData.update_time = time;
        formData.status = 1; //0表示数据有用

        sqlString = "INSERT INTO " + tableName + " SET ?";
    }

    var sqlOptions = {
        sql: sqlString,
        params: formData
    }
    return this.executeMysql(sqlOptions);
}

fn.batchAdd = function (options) {
    var formData = options.form;
    var tableName = this.pool.escapeId(options.tableName);
    var time = new Date();
    var isUpdate = false;
    for (var i = 0, j = formData.length; i < j; i++) {
        formData[i].push(time);
        formData[i].push(time);
        formData[i].push(1);
    }

    var sqlString = "INSERT INTO " + tableName + " (atom_id,name,alias_name,is_intable,is_key,t_index,axis,yAxis_color,yAxis_type,show_label,smart_unit,is_merge,operator,updater,create_time,update_time,status) values ?";
    var sqlOptions = {
        sql: sqlString,
        params: [formData]
    }
    return this.executeMysql(sqlOptions);
}

fn.batchAddNewsChannel = function (options) {
    var formData = options.form;
    var tableName = this.pool.escapeId(options.tableName);
    var sqlString = "INSERT INTO " + tableName + " (news_id,channel_id,create_time) values ?";

    var sqlOptions = {
        sql: sqlString,
        params: [formData]
    }
    return this.executeMysql(sqlOptions);
}

fn.batchUpdate = function (options) {
    var formData = options.form;
    var tableName = this.pool.escapeId(options.tableName);
    var time = new Date();
    for (var i = 0, j = formData.length; i < j; i++) {
        formData[i].push(time);
        formData[i].push(time);
        formData[i].push(1);
    }

    var sqlString = "INSERT INTO " + tableName + " (id, atom_id,name,alias_name,is_intable,is_key,t_index,create_time, update_time,status) values ?";
    sqlString += ' on duplicate key update id=values(id), atom_id=values(atom_id), name=values(name), alias_name=values(alias_name),is_intable=values(is_intable),is_key=values(is_key),t_index=values(t_index),create_time=values(create_time), update_time=values(update_time),status=values(status)';
    var sqlOptions = {
        sql: sqlString,
        params: [formData]
    }
    return this.executeMysql(sqlOptions);
}


fn.getDataById = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    this.wheres = this.where(options.wheres);
    var params = {
        sql: 'SELECT * FROM ' + this.tableName + ' where ' + this.wheres + ';',
        params: []
    }
    console.log('get data by sql:: ' + params.sql);
    return this.executeMysql(params);
}

fn.delete = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    this.wheres = this.where(options.wheres);
    var params = {
        sql: 'UPDATE ' + this.tableName + ' SET status = 0, update_time = now()' + ' where ' + this.wheres + ';',
        params: []
    }
    return this.executeMysql(params);
}

fn.addBack = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    this.wheres = this.where(options.wheres);
    var params = {
        sql: 'UPDATE ' + this.tableName + ' SET status = 1, update_time = now()' + ' where ' + this.wheres + ';',
        params: []
    }
    return this.executeMysql(params);
}

fn.pass = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    this.wheres = this.where(options.wheres);
    var params = {
        sql: 'UPDATE ' + this.tableName + ' SET verified = 1, update_time = now()' + ' where ' + this.wheres + ';',
        params: []
    }
    return this.executeMysql(params);
}
fn.batchDelete = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    // this.ids = options.ids;
    var ids = options.ids;
    var params = {
        sql: 'delete from ' + this.tableName + ' where id in (?)',
        params: [ids]
    }
    return this.executeMysql(params);
}

fn.batchPass = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    // this.ids = options.ids;
    var ids = options.ids;
    var params = {
        sql: 'UPDATE ' + this.tableName + ' SET verified = 1, update_time = now()' + ' where id in (?)',
        params: [ids]
    }
    return this.executeMysql(params);
}

fn.deleteByParams = function (options) {
    this.tableName = this.pool.escapeId(options.table);
    this.wheres = this.where(options.wheres);
    var params = {
        sql: 'delete from ' + this.tableName + ' where ' + this.wheres + ';',
        params: []
    }
    return this.executeMysql(params);
}

module.exports = commonDb